﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DBtest
{
    class SQLiteDB
    {
        #region Variables&Properties
        private SQLiteConnection sql_con;
        private SQLiteCommand sql_cmd;
        private SQLiteDataAdapter db;
        private DataSet ds = new DataSet();
        
        private DataTable dt = new DataTable();
        /// <summary>
        /// Holds the result of the latest query performed with SQLQuery()
        /// </summary>
        public DataTable QueryResult
        {
            get { return dt; }
        }

        public string DBFile { get; set; } 
        #endregion

        private void SetConnection()
        {
            sql_con = new SQLiteConnection
                ("Data Source=" + DBFile + ";Version=3;New=False;Compress=True;");
        } 

        /// <summary>
        /// Used to execute commands such as INSERT, CREATE, UPDATE on the database
        /// </summary>
        /// <example>SQLCommand("CREATE TABLE test (number INT, string TEXT);")</example>
        /// <param name="txtQuery">the SQL query in string form</param>
        public void SQLCommand(string txtQuery) 
        { 
            SetConnection(); 
            sql_con.Open(); 
            sql_cmd = sql_con.CreateCommand(); 
            sql_cmd.CommandText=txtQuery; 
            sql_cmd.ExecuteNonQuery(); 
            sql_con.Close(); 
        }

        /// <summary>
        /// Used to query the database with SQL, the result is accessed through the QueryResult Property
        /// </summary>
        /// <example>SQLQuery("SELECT * FROM test;")</example>
        /// <param name="txtQuery">the SQL query in string form</param>
        public void SQLQuery(string txtQuery)
        {
            SetConnection();
            sql_con.Open();
            sql_cmd = sql_con.CreateCommand();
            db = new SQLiteDataAdapter(txtQuery, sql_con);
            ds.Reset();
            db.Fill(ds);
            dt = ds.Tables[0];
            sql_con.Close(); 
        }
    }
}
